/**
	EXEC SP_SPECIFICATION_FILE_SETUP_REPORT 'Assembly', 0, 0
TMR791D-FX-O-02-HB2-L
*/
ALTER PROC SP_SPECIFICATION_FILE_SETUP_REPORT ( @SEARCH_PROCESS_TYPE VARCHAR(50),
										@SEARCH_PROCESSSTEP_ID INT,
										@SEARCH_VENDOR_ID INT
										)
AS
BEGIN
	DECLARE @PROCESS_TYPE VARCHAR(50)
	DECLARE @PROCESSSTEP_ID INT	
	DECLARE @VENDOR_ID INT
	DECLARE @SPEC_FILE_NAME VARCHAR(50)
	DECLARE @REVISION VARCHAR(50)
	DECLARE @PART_NUMBER VARCHAR(50)
	DECLARE @VENDOR_NAME VARCHAR(50)
	DECLARE @EFFECT_DATE DATETIME
	DECLARE @MARKING_FIRST_LINE VARCHAR(50)
	DECLARE @MARKING_SECOND_LINE VARCHAR(50)
	DECLARE @MARKING_THIRD_LINE VARCHAR(50)
	DECLARE @MARKING_FOURTH_LINE VARCHAR(50)
	DECLARE @MARKING_FIFTH_LINE VARCHAR(50)
	DECLARE @MARKING_SIXTH_LINE VARCHAR(50)
	DECLARE @SPECFILE_ID INT

	DECLARE @RESULTS TABLE ( PROCESS_TYPE VARCHAR(50),
							SPECFILE_ID INT,
							SPEC_FILE_NAME VARCHAR(50),
							PROCESSSTEP_ID INT,
							PART_NUMBER VARCHAR(50),
							VENDOR_ID INT,
							VENDOR_NAME VARCHAR(50),
							REVISION_NUMBER VARCHAR(50),
							MARKING_FIRST_LINE VARCHAR(50),
							MARKING_SECOND_LINE VARCHAR(50),
							MARKING_THIRD_LINE VARCHAR(50),
							MARKING_FOURTH_LINE VARCHAR(50),
							MARKING_FIFTH_LINE VARCHAR(50),
							MARKING_SIXTH_LINE VARCHAR(50),
							EFFECT_DATE DATETIME
						)
							
	DECLARE @SPEC_FILE_CURSOR_STR VARCHAR(1300)
	SET @SPEC_FILE_CURSOR_STR = N' DECLARE SPEC_FILE_CURSOR CURSOR FOR 
							SELECT PROCESS_TYPE, 
									ST.SPECFILE_ID,
									SPEC_FILE_NAME,
									REVISION,
									PST.PROCESSSTEP_ID, 
									(SELECT PART_NUMBER FROM PRODUCT WHERE PROCESSSTEP_ID = PST.PROCESSSTEP_ID) AS PART_NUMBER, 
									PST.VENDOR_ID,
									(CASE WHEN PST.VENDOR_ID = 0
										THEN ''ALL VENDORS''
										ELSE
											(SELECT V_NAME FROM VENDOR WHERE VENDOR_ID = PST.VENDOR_ID) 
									END)
									AS VENDOR_NAME, 
									EFFECT_DATE 
							FROM SPEC_TABLE ST, PRODUCT_SPEC_TABLE PST, Product P
							WHERE ST.SPECFILE_ID = PST.SPECFILE_ID AND PST.ProcessStep_ID = P.ProcessStep_ID
							AND P.Obsolete = 1 AND P.Part_Status <> ''Obsolete''
							AND ST.SPECFILE_ID = ( SELECT MAX( ST2.SPECFILE_ID ) FROM SPEC_TABLE ST2 
												WHERE ST2.SPEC_FILE_NAME = ST.SPEC_FILE_NAME AND ST2.PROCESS_TYPE = ST.PROCESS_TYPE AND ST2.Revision = ST.Revision 
												)'
	IF ( @SEARCH_PROCESS_TYPE IS NOT NULL AND @SEARCH_PROCESS_TYPE != '0' )
	BEGIN
		SET @SPEC_FILE_CURSOR_STR = @SPEC_FILE_CURSOR_STR + ' AND ST.PROCESS_TYPE = ''' + @SEARCH_PROCESS_TYPE + ''''
	END
	IF ( @SEARCH_PROCESSSTEP_ID IS NOT NULL AND @SEARCH_PROCESSSTEP_ID != '0' )
	BEGIN
		SET @SPEC_FILE_CURSOR_STR = @SPEC_FILE_CURSOR_STR + ' AND PST.PROCESSSTEP_ID = ' + CONVERT(VARCHAR,@SEARCH_PROCESSSTEP_ID)
	END
	IF ( @SEARCH_VENDOR_ID IS NOT NULL AND @SEARCH_VENDOR_ID != '0' )
	BEGIN
		SET @SPEC_FILE_CURSOR_STR = @SPEC_FILE_CURSOR_STR + ' AND ( PST.VENDOR_ID = 0 OR PST.VENDOR_ID = ' + CONVERT(VARCHAR,@SEARCH_VENDOR_ID) + ')'
	END

	SET @SPEC_FILE_CURSOR_STR = @SPEC_FILE_CURSOR_STR + ' ORDER BY PST.PROCESSSTEP_ID, VENDOR_ID'

	PRINT @SPEC_FILE_CURSOR_STR
	EXEC (@SPEC_FILE_CURSOR_STR)
	
	OPEN SPEC_FILE_CURSOR
	FETCH NEXT FROM SPEC_FILE_CURSOR INTO @PROCESS_TYPE, @SPECFILE_ID, @SPEC_FILE_NAME, @REVISION, @PROCESSSTEP_ID, @PART_NUMBER, @VENDOR_ID, @VENDOR_NAME, @EFFECT_DATE
	WHILE ( @@FETCH_STATUS = 0 )
	BEGIN
		SET @MARKING_FIRST_LINE = ''
		SET @MARKING_SECOND_LINE = ''
		SET @MARKING_THIRD_LINE = ''
		SET @MARKING_FOURTH_LINE = ''
		SET @MARKING_FIFTH_LINE = ''
		SET @MARKING_SIXTH_LINE = ''
		
		IF ( (SELECT COUNT(*) FROM @RESULTS WHERE PROCESSSTEP_ID = @PROCESSSTEP_ID AND ( VENDOR_NAME = 'ALL VENDORS' OR VENDOR_NAME = @VENDOR_NAME)) = 0 )
		BEGIN
			IF ( @SEARCH_VENDOR_ID IS NOT NULL AND @SEARCH_VENDOR_ID > 0 )
			BEGIN
				SELECT @MARKING_FIRST_LINE = MARKING_FIRST_LINE,
						@MARKING_SECOND_LINE = MARKING_SECOND_LINE,
						@MARKING_THIRD_LINE = MARKING_THIRD_LINE,
						@MARKING_FOURTH_LINE = MARKING_FOURTH_LINE,
						@MARKING_FIFTH_LINE = MARKING_FIFTH_LINE,
						@MARKING_SIXTH_LINE = MARKING_SIXTH_LINE
				FROM ASSEMBLY_RELEASE_MARKING_INFO_TABLE WHERE PROCESSSTEP_ID = @PROCESSSTEP_ID AND VENDOR_ID = @SEARCH_VENDOR_ID
				SELECT @VENDOR_ID = VENDOR_ID, @VENDOR_NAME = V_NAME FROM VENDOR WHERE VENDOR_ID = @SEARCH_VENDOR_ID
			END
			ELSE IF ( @VENDOR_ID > 0 )
			BEGIN
				SELECT @MARKING_FIRST_LINE = MARKING_FIRST_LINE,
						@MARKING_SECOND_LINE = MARKING_SECOND_LINE,
						@MARKING_THIRD_LINE = MARKING_THIRD_LINE,
						@MARKING_FOURTH_LINE = MARKING_FOURTH_LINE,
						@MARKING_FIFTH_LINE = MARKING_FIFTH_LINE,
						@MARKING_SIXTH_LINE = MARKING_SIXTH_LINE
				FROM ASSEMBLY_RELEASE_MARKING_INFO_TABLE WHERE PROCESSSTEP_ID = @PROCESSSTEP_ID AND VENDOR_ID = @VENDOR_ID
			END
			ELSE --WHICH VENDOR TO CONSIDER
			BEGIN
				--INSERTING ALL VENDORS MARKING LINES
				INSERT INTO @RESULTS( PROCESS_TYPE, SPECFILE_ID, SPEC_FILE_NAME, PROCESSSTEP_ID, PART_NUMBER, VENDOR_ID, VENDOR_NAME, REVISION_NUMBER, 
								MARKING_FIRST_LINE, MARKING_SECOND_LINE, MARKING_THIRD_LINE, MARKING_FOURTH_LINE, MARKING_FIFTH_LINE, MARKING_SIXTH_LINE, EFFECT_DATE )
						SELECT @PROCESS_TYPE, @SPECFILE_ID, @SPEC_FILE_NAME, @PROCESSSTEP_ID, @PART_NUMBER, VENDOR_ID, (SELECT V_NAME FROM VENDOR WHERE VENDOR_ID = ARMIT.VENDOR_ID), @REVISION,
							MARKING_FIRST_LINE, MARKING_SECOND_LINE, MARKING_THIRD_LINE, MARKING_FOURTH_LINE, MARKING_FIFTH_LINE, MARKING_SIXTH_LINE, @EFFECT_DATE
						FROM ASSEMBLY_RELEASE_MARKING_INFO_TABLE ARMIT WHERE PROCESSSTEP_ID = @PROCESSSTEP_ID 


			END	
			INSERT INTO @RESULTS( PROCESS_TYPE, SPECFILE_ID, SPEC_FILE_NAME, PROCESSSTEP_ID, PART_NUMBER, VENDOR_ID, VENDOR_NAME, REVISION_NUMBER, 
								MARKING_FIRST_LINE, MARKING_SECOND_LINE, MARKING_THIRD_LINE, MARKING_FOURTH_LINE, MARKING_FIFTH_LINE, MARKING_SIXTH_LINE, EFFECT_DATE )
						VALUES( @PROCESS_TYPE, @SPECFILE_ID, @SPEC_FILE_NAME, @PROCESSSTEP_ID, @PART_NUMBER, @VENDOR_ID, @VENDOR_NAME, @REVISION,
								@MARKING_FIRST_LINE, @MARKING_SECOND_LINE, @MARKING_THIRD_LINE, @MARKING_FOURTH_LINE, @MARKING_FIFTH_LINE, @MARKING_SIXTH_LINE, @EFFECT_DATE)
		END
		ELSE --NEED TO UPDATE THE LATEST SPEC FILE
		BEGIN
			IF ( (SELECT EFFECT_DATE FROM @RESULTS WHERE  PROCESSSTEP_ID = @PROCESSSTEP_ID AND VENDOR_NAME = @VENDOR_NAME ) < @EFFECT_DATE )
			BEGIN
				UPDATE @RESULTS SET SPECFILE_ID = @SPECFILE_ID, SPEC_FILE_NAME = @SPEC_FILE_NAME, REVISION_NUMBER = @REVISION, EFFECT_DATE = @EFFECT_DATE WHERE PROCESSSTEP_ID = @PROCESSSTEP_ID AND ( VENDOR_NAME = 'ALL VENDORS' OR VENDOR_NAME = @VENDOR_NAME)
			END
		END
		FETCH NEXT FROM SPEC_FILE_CURSOR INTO @PROCESS_TYPE, @SPECFILE_ID, @SPEC_FILE_NAME, @REVISION, @PROCESSSTEP_ID, @PART_NUMBER, @VENDOR_ID, @VENDOR_NAME, @EFFECT_DATE
	END
	CLOSE SPEC_FILE_CURSOR
	DEALLOCATE SPEC_FILE_CURSOR

	SELECT	PART_NUMBER AS [PART NUMBER],
			VENDOR_NAME AS [VENDOR NAME],
			PROCESS_TYPE AS [PROCESS TYPE], 
			SPEC_FILE_NAME AS [SPECIFICATION FILE NAME] , 
			MARKING_FIRST_LINE AS [MARKING FIRST LINE], 
			MARKING_SECOND_LINE AS [MARKING SECOND LINE], 
			MARKING_THIRD_LINE AS [MARKING THIRD LINE], 
			MARKING_FOURTH_LINE AS [MARKING FOURTH LINE], 
			MARKING_FIFTH_LINE AS [MARKING FIFTH LINE], 
			MARKING_SIXTH_LINE AS [MARKING SIXTH LINE] 
	FROM @RESULTS
	ORDER BY PROCESS_TYPE, PART_NUMBER, VENDOR_NAME
END
